package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.PageResult;
import com.rockcent.common.common.SQLListDoing;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.common.common.SQLPageDoing;
import com.rockcent.mall.lib.domain.enumclass.MerchantOrderSourceType;
import com.rockcent.mall.lib.domain.enumclass.ProductSourceType;
import com.rockcent.mall.lib.domain.enumclass.StatististicsType;
import com.rockcent.mall.lib.dto.ChannelDto;
import com.rockcent.mall.lib.dto.ChannelStatisticsChartDto;
import com.rockcent.mall.lib.dto.MerchantChannelStatisticsDto;
import com.rockcent.mall.lib.dto.ProductStatisticsDto;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * Created by zhangjh on 2016/11/2.
 */
@Repository
public class MerchantChannelStatisticsRepositoryImpl implements MerchantChannelStatisticsRepositoryCustom {


    private   Logger log = LoggerFactory.getLogger(MerchantChannelStatisticsRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private ObjectMapper objectMapper;

    /**
     *
     * @param page
     * @param mallId
     * @param merchantId

     * @return
     */
    @Override
    public PageResult<MerchantChannelStatisticsDto> getChannelStatisticsInfo(PageResult page, Long mallId, Long merchantId) {

        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT c.`name`,ct.type_name FROM channel c " +
                    "LEFT JOIN channel_type ct ON ct.id=c.`channel_type_id`" +
                    "WHERE c.`is_delete`='NO'" +
                    " AND c.mall_id=:mallId" +
                    " AND c.`merchant_id`=:merchantId");
            params.put("mallId",mallId);
            params.put("merchantId",merchantId);
            fields.append("c.id as channelId,c.`name`,ct.type_name");


            log.debug("channel static sql:" + sql.toString());

        };

        return doing.go(page, MerchantChannelStatisticsDto.class, objectMapper, jdbcTemplate, log);
    }


    @Override
    public Long getChannelProductCategoryCount(Long channelId, Long mallId, Long merchantId) {

        /**
         * select count(*) from channel_statistics cs where cs.`channel_id`=42
         */
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {

            sql.append("select %s from channel_category cc left join channel c on c.id=cc.channel_product_types_id");
            sql.append(" WHERE  cc.channel_product_types_id=:channelId");
            sql.append(" AND c.`mall_id`=:mallId");
            sql.append(" AND c.`merchant_id`=:merchantId");
            //sql.append(" AND p.type ='GOODS','SERVICE'");
            //sql.append(" AND p.market_id IS NULL");
            params.put("channelId", channelId);
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);

            fields.append("count(cc.category_id)");
            log.debug("channel static sql:" + sql.toString());

        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }


    @Override
    public PageResult<ProductStatisticsDto> getChannelProductStatisticsDetails(Long channelId, Long mallId, Long merchantId, String type, MerchantOrderSourceType sourceType, String startDate, String endDate, String productName, PageResult pageResult) {
        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {

            sql.append("select %s from `product_event` pe");
            sql.append(" left join product p on p.`id`=pe.`product_id`");
            sql.append(" LEFT JOIN channel c on c.`id`=pe.`channel`");
            sql.append(" where pe.`is_delete`='NO'");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId");
            sql.append(" AND c.`id`=:channelId");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            params.put("channelId", channelId);

            if (type != null && ProductSourceType.ALL != ProductSourceType.valueOf(type)) {
                sql.append(" AND p.`type`=:type");
                params.put("type", type);
            }
            if (sourceType != null && sourceType != MerchantOrderSourceType.ALL) {
                if (sourceType == MerchantOrderSourceType.MARKET) {
                    sql.append(" AND p.`market_id` is not null");
                } else {
                    sql.append(" AND p.`market_id` is  null");
                }
            }
            if (StringUtils.isNotBlank(productName)) {
                sql.append(" AND (p.`name` like:productName OR p.id like:barCode)");
                params.put("productName", "%" + productName + "%");
                params.put("barCode", "%" + productName + "%");
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append("pe.id,p.id as productId,p.`name` as productName, p.`sell_price`");

            log.debug("channel static sql:" + sql.toString());

        };

        return doing.go(pageResult, ProductStatisticsDto.class, objectMapper, jdbcTemplate, log);
    }


    /**
     *
     * 统计访问人数
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateCustomerCountChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {

        /**
         * 统计访问人数
         */
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT c.`name` as channelName,c.`id`,dateCount,viewCustomer FROM channel c LEFT JOIN (");
            sql.append("SELECT  %s FROM product_event pe");
            sql.append(" LEFT JOIN channel c ON pe.`channel`=c.`id`");
            sql.append(" WHERE pe.`is_delete`='NO'");
            sql.append(" AND pe.event_type IN('CLICKED','VIEWED')");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId AND c.`name` IS NOT NULL");
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1"); //DATE(pe.date_created)=CURDATE()-1
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                if(startDate!=null){
                    sql.append(" AND pe.date_created>=:startDate");
                    params.put("startDate", startDate);
                }
                if(endDate!=null){
                    sql.append("  and pe.date_created<=:endDate");
                    params.put("endDate", endDate);
                }


            }
            //sql.append(" GROUP BY pe.`channel`,pe.`date_created`");
            if(type!=null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" GROUP BY pe.`channel`,pe.`date_created`");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m-%d')");

                } else {
                    sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m')");
                }
            }else{
                sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m-%d')");
            }
            sql.append(") b ON b.id=c.id  WHERE c.`is_delete`='NO' AND c.`mall_id`=:SecondMallId AND c.`merchant_id`=:SecondMerchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("SecondMallId", mallId);
            params.put("SecondMerchantId", merchantId);



            if(type!=null) {
                if (type == StatististicsType.YESTERDAY) {
                    fields.append(" c.id,c.`name` as channelName ,pe.`date_created` as dateCount ,COUNT(distinct pe.`buyer_id`) as viewCustomer");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    fields.append(" c.id,c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d') as dateCount ,COUNT(distinct pe.`buyer_id`) as viewCustomer");
                    //  fields.append(" c.id,c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d') as date ,COUNT(pe.`event_type`) as viewCount");
                } else {
                    fields.append(" c.id,c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d') as dateCount ,COUNT(distinct pe.`buyer_id`) as viewCustomer");
                }
            }else{
                fields.append(" c.id,c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d') as dateCount ,COUNT(distinct pe.`buyer_id`) as viewCustomer");
            }

            log.debug("channel static sql:" + sql.toString());

        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     *
     * 统计访问次数
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateViewCountChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {
        /**
         * 统计访问次数
         */
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT c.`name` as channelName,c.`id`,dateCount,viewCount FROM channel c LEFT JOIN (");
            sql.append("SELECT  %s FROM product_event pe");
            sql.append(" LEFT JOIN channel c ON pe.`channel`=c.`id`");
            sql.append(" WHERE pe.`is_delete`='NO'");

            sql.append(" AND pe.event_type IN('CLICKED','VIEWED')");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId AND c.`name` IS NOT NULL");
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1");
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                if(startDate!=null){
                    sql.append(" AND pe.date_created>=:startDate");
                    params.put("startDate", startDate);
                }
                if(endDate!=null){
                    sql.append("  and pe.date_created<=:endDate");
                    params.put("endDate", endDate);
                }
            }
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" GROUP BY pe.`channel`,pe.`date_created`");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.date_created, '%Y-%m-%d')");
                    // sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m-%d') )b ON a.channel=b.id;");
                } else {
                    {
                        sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.date_created, '%Y-%m')");
                        //sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m') )b ON a.channel=b.id;");
                    }
                }
            } else {
                sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.date_created, '%Y-%m-%d')");
            }
            sql.append(") b ON b.id=c.id  WHERE c.`is_delete`='NO' AND c.`mall_id`=:secondMallId AND c.`merchant_id`=:SecondMerchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("secondMallId", mallId);
            params.put("SecondMerchantId", merchantId);
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    fields.append(" c.id,c.`name` as channelName ,pe.`date_created` as dateCount ,COUNT(pe.`event_type`) as viewCount");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    fields.append(" c.id,c.`name` as channelName ,DATE_FORMAT(pe.date_created,'%Y-%m-%d') as dateCount ,COUNT(pe.`event_type`) as viewCount");

                } else {
                    fields.append(" c.id,c.`name` as channelName ,DATE_FORMAT(pe.date_created,'%Y-%m-%d') as dateCount ,COUNT(pe.`event_type`) as viewCount");

                }
            } else {
                fields.append(" c.id,c.`name` as channelName ,DATE_FORMAT(pe.date_created,'%Y-%m-%d') as dateCount ,COUNT(pe.`event_type`) as viewCount");
            }
            log.debug("channel static sql:" + sql.toString());

        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     *  统计下单数结果集
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateSaleCountChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {
        /**
         * 统计下单数结果集
         */
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT c.`name` as channelName,c.`id`,dateCount,saleCount FROM channel c LEFT JOIN (");
            sql.append("SELECT  %s FROM product_event pe");
            sql.append(" LEFT JOIN channel c ON pe.`channel`=c.`id`");
            sql.append(" WHERE pe.`is_delete`='NO'");

            sql.append(" AND pe.event_type ='ORDERED'");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId AND c.`name` IS NOT NULL");
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1");
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                if(startDate!=null){
                    sql.append(" AND pe.date_created>=:startDate");
                    params.put("startDate", startDate);
                }
                if(endDate!=null){
                    sql.append("  and pe.date_created<=:endDate");
                    params.put("endDate", endDate);
                }
            }
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" GROUP BY pe.`channel`,pe.`date_created`");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m-%d')");
                    // sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m-%d') )b ON a.channel=b.id;");
                } else {
                    sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m')");
                    //sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m') )b ON a.channel=b.id;");
                }
            }else{
                sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m-%d')");
            }

            sql.append(") b ON b.id=c.id  WHERE c.`is_delete`='NO' AND c.`mall_id`=:secondMallId AND c.`merchant_id`=:SecondMerchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("secondMallId", mallId);
            params.put("SecondMerchantId", merchantId);

            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    fields.append("c.id, c.`name` as channelName ,pe.`date_created`as dateCount ,sum(pe.`num`) as saleCount");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    fields.append("c.id, c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d')  as dateCount ,sum(pe.`num`) as saleCount");
                } else {
                    fields.append("c.id, c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m')  as dateCount ,sum(pe.`num`) as saleCount");
                }
            } else {
                fields.append("c.id, c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d')  as dateCount ,sum(pe.`num`) as saleCount");
            }

            log.debug("channel static sql:" + sql.toString());

        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }


    /**
     *  统计下单人数结果集
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateSaleCustomerCountChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {
        /**
         *  统计下单人数结果集
         */
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT c.`name` as channelName,c.`id`,dateCount,saleCustomerCount FROM channel c LEFT JOIN (");
            sql.append("SELECT  %s FROM product_event pe");
            sql.append(" LEFT JOIN channel c ON pe.`channel`=c.`id`");
            sql.append(" WHERE pe.`is_delete`='NO'");

            sql.append(" AND pe.event_type ='ORDERED'");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId AND c.`name` IS NOT NULL");
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1");
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                if(startDate!=null){
                    sql.append(" AND pe.date_created>=:startDate");
                    params.put("startDate", startDate);
                }
                if(endDate!=null){
                    sql.append("and pe.date_created<=:endDate");
                    params.put("endDate", endDate);
                }
            }

            if (type == StatististicsType.YESTERDAY) {
                sql.append(" GROUP BY pe.`channel`,pe.`date_created`");
            } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m-%d')");
                // sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m-%d') )b ON a.channel=b.id;");
            } else {
                sql.append(" GROUP BY pe.`channel`,DATE_FORMAT(pe.`date_created`, '%Y-%m')");
                //sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m') )b ON a.channel=b.id;");
            }

            sql.append(") b ON b.id=c.id  WHERE c.`is_delete`='NO' AND c.`mall_id`=:secondMallId AND c.`merchant_id`=:secondMerchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("secondMallId", mallId);
            params.put("secondMerchantId", merchantId);

            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    fields.append("c.id, c.`name` as channelName ,pe.`date_created` as dateCount ,COUNT(distinct pe.`buyer_id`) as saleCustomerCount");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    fields.append("c.id, c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d')  as dateCount ,COUNT(distinct pe.`buyer_id`) as saleCustomerCount");
                    //fields.append(" c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d')  as date ,sum(pe.`num`) as saleCount");
                } else {
                    fields.append("c.id, c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m')  as dateCount ,COUNT(distinct pe.`buyer_id`) as saleCustomerCount");
                    //  fields.append(" c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m')  as date ,sum(pe.`num`) as saleCount");
                }
            } else {
                fields.append("c.id, c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m-%d')  as dateCount ,COUNT(distinct pe.`buyer_id`) as saleCustomerCount");
            }

            log.debug("channel static sql:" + sql.toString());

        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     *  下单转化率
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateSaleRateChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {
        /***
         * SELECT channelName,saleTotal,saleCount,date_created,(saleCount/saleTotal)*100
         FROM (SELECT SUM(pe.`num`) AS saleTotal,pe.`channel` FROM  product_event pe
         WHERE pe.`is_delete` = 'NO'
         AND pe.event_type = 'ORDERED'
         AND pe.`mall_id` = 1
         AND pe.`merchant_id` = 74)a
         LEFT JOIN (
         SELECT
         c.`id`,
         c.`name` AS channelName,
         pe.`date_created`,
         SUM(pe.`num`) AS saleCount
         FROM
         product_event pe
         LEFT JOIN channel c
         ON pe.`channel` = c.`id`
         WHERE pe.`is_delete` = 'NO'
         AND pe.event_type = 'ORDERED'
         AND pe.`mall_id` = 1
         AND pe.`merchant_id` = 74
         AND c.`name` IS NOT NULL
         AND TO_DAYS(NOW()) - TO_DAYS(pe.date_created) <= 1
         GROUP BY pe.`channel`,
         pe.`date_created` ) b ON a.channel=b.id;
         */

        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT ch.`name` as channelName,ch.`id`,dateCount,saleCount,saleConversionRate FROM channel ch LEFT JOIN (");
            sql.append("SELECT %s  FROM (SELECT SUM(pe.`num`) AS saleTotal,pe.`channel` FROM  product_event pe " +
                    "         WHERE pe.`is_delete` = 'NO' " +
                    "         AND pe.event_type = 'ORDERED' " +
                    "         AND pe.`mall_id` =:mallId " +
                    "         AND pe.`merchant_id` =:merchantId)a " +
                    "         LEFT JOIN ( " +
                    "         SELECT " +
                    "         c.`id`, " +
                    "         c.`name` AS channelName, " +
                    "         pe.`date_created`, " +
                    "         SUM(pe.`num`) AS saleCount " +
                    "         FROM " +
                    "         product_event pe " +
                    "         LEFT JOIN channel c " +
                    "         ON pe.`channel` = c.`id` " +
                    "         WHERE pe.`is_delete` = 'NO' " +
                    "         AND pe.event_type = 'ORDERED' " +
                    "         AND pe.`mall_id` = :secondMallId " +
                    "         AND pe.`merchant_id` =:SecondMerchantId " +
                    "         AND c.`name` IS NOT NULL");

            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1");
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                if(startDate!=null){
                    sql.append(" AND pe.date_created>=:startDate");
                    params.put("startDate", startDate);
                }
                if(endDate!=null){
                    sql.append("and pe.date_created<=:endDate");
                    params.put("endDate", endDate);
                }
            }
            if(type!=null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" GROUP BY pe.`channel`,pe.`date_created` )b ON a.channel=b.id");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m-%d') )b ON a.channel=b.id");
                } else {
                    sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m') )b ON a.channel=b.id");
                }
            }else {
                sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m-%d') )b ON a.channel=b.id");
            }
            sql.append(") d ON d.id=ch.id  WHERE ch.`is_delete`='NO' AND ch.`mall_id`=:thirdMallId AND ch.`merchant_id`=:thirdMerchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("secondMallId", mallId);
            params.put("SecondMerchantId", merchantId);

            params.put("thirdMallId", mallId);
            params.put("thirdMerchantId", merchantId);

            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    fields.append(" channel AS id,channelName,saleTotal,saleCount,date_created as dateCount,  case when  (saleCount/saleTotal)*100 is null then 0 else (saleCount/saleTotal)*100 end  as saleConversionRate");
                } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                    fields.append(" channel AS id,channelName,saleTotal,saleCount,DATE_FORMAT(`date_created`,'%Y-%m-%d')  as dateCount ,  case when  (saleCount/saleTotal)*100 is null then 0 else (saleCount/saleTotal)*100 end  as saleConversionRate");
                } else {
                    fields.append(" channel AS id,channelName,saleTotal,saleCount,DATE_FORMAT(`date_created`,'%Y-%m-%d')  as dateCount ,  case when  (saleCount/saleTotal)*100 is null then 0 else (saleCount/saleTotal)*100 end  as saleConversionRate");
                    //  fields.append(" c.`name` as channelName ,DATE_FORMAT(pe.`date_created`,'%Y-%m')  as date ,sum(pe.`num`) as saleCount");
                }
            } else {
                fields.append(" channel AS id,channelName,saleTotal,saleCount,DATE_FORMAT(`date_created`,'%Y-%m-%d')  as dateCount ,  case when  (saleCount/saleTotal)*100 is null then 0 else (saleCount/saleTotal)*100 end  as saleConversionRate");
            }
            log.debug("channel static sql:" + sql.toString());
        };


        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);


    }

    /**
     * 核销转化率图表
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateVerificationRateChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT ch.`name` as channelName,ch.`id`,dateCount,verificationTotal,verificationCount,saleConversionRate FROM channel ch LEFT JOIN (");
            sql.append("SELECT %s  FROM (SELECT SUM(pe.`num`) AS verificationTotal,pe.`channel` FROM  product_event pe " +
                    "         WHERE pe.`is_delete` = 'NO' " +
                    "         AND pe.event_type = 'VERIFIED' " +
                    "         AND pe.`mall_id` =:mallId " +
                    "         AND pe.`merchant_id` =:merchantId)a " +
                    "         LEFT JOIN ( " +
                    "         SELECT " +
                    "         c.`id`, " +
                    "         c.`name` AS channelName, " +
                    "         pe.`date_created`, " +
                    "         SUM(pe.`num`) AS verificationCount " +
                    "         FROM " +
                    "         product_event pe " +
                    "         LEFT JOIN channel c " +
                    "         ON pe.`channel` = c.`id` " +
                    "         WHERE pe.`is_delete` = 'NO' " +
                    "         AND pe.event_type = 'VERIFIED' " +
                    "         AND pe.`mall_id` = :secondMallId " +
                    "         AND pe.`merchant_id` =:SecondMerchantId " +
                    "         AND c.`name` IS NOT NULL");

            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1");
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                sql.append(" AND pe.date_created>=:startDate and pe.date_created<=:endDate");
                params.put("startDate", startDate);
                params.put("endDate", endDate);
            }
        if(type!=null) {
            if (type == StatististicsType.YESTERDAY) {
                sql.append(" GROUP BY pe.`channel`,pe.`date_created` )b ON a.channel=b.id");
            } else if (type == StatististicsType.MONTH || type == StatististicsType.WEEK) {
                sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m-%d') )b ON a.channel=b.id");
            } else {
                sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m') )b ON a.channel=b.id");
            }
        }else{
            sql.append(" GROUP BY pe.`channel`, DATE_FORMAT(pe.`date_created`, '%Y-%m-%d') )b ON a.channel=b.id");
        }
            sql.append(") d ON d.id=ch.id  WHERE ch.`is_delete`='NO' AND ch.`mall_id`=:mallId AND ch.`merchant_id`=:thirdMerchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("secondMallId", mallId);
            params.put("SecondMerchantId", merchantId);
            params.put("thirdMallId", mallId);
            params.put("thirdMerchantId", merchantId);
            fields.append("channel AS id,channelName,verificationTotal,verificationCount,date_created as dateCount,  case when  (verificationCount/verificationTotal)*100 is null then 0 else (verificationCount/verificationTotal)*100 end  as saleConversionRate");
            log.debug("channel static sql:" + sql.toString());
        };

        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销人数
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateVerificationCustomerCountChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {

        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT ch.`name` as channelName,ch.`id`,dateCount,verificationCustomerCount FROM channel ch LEFT JOIN (");
            sql.append("SELECT  %s FROM product_event pe");
            sql.append(" LEFT JOIN channel c ON pe.`channel`=c.`id`");
            sql.append(" WHERE pe.`is_delete`='NO'");

            sql.append(" AND pe.event_type ='VERIFIED'");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId AND c.`name` IS NOT NULL");
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1");
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                sql.append(" AND pe.date_created>=:startDate and pe.date_created<=:endDate");
                params.put("startDate", startDate);
                params.put("endDate", endDate);
            }
            sql.append(" GROUP BY pe.`channel`,pe.`date_created`");
            sql.append(") d ON d.id=ch.id  WHERE ch.`is_delete`='NO' AND ch.`mall_id`=:thirdMallId AND ch.`merchant_id`=:thirdMerchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("thirdMallId", mallId);
            params.put("thirdMerchantId", merchantId);
            fields.append(" c.id,c.`name` as channelName ,pe.`date_created` as dateCount ,COUNT(distinct pe.`buyer_id`) as verificationCustomerCount");
            log.debug("channel static sql:" + sql.toString());
        };

        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销数统计图表
     * @param startDate
     * @param endDate
     * @param mallId
     * @param merchantId
     * @param type
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> genarateVerificationCountChartByCondition(String startDate, String endDate, Long mallId, Long merchantId, StatististicsType type) {

        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT ch.`name` as channelName,ch.`id`,dateCount,verificationCount FROM channel ch LEFT JOIN (");
            sql.append("SELECT  %s FROM product_event pe");
            sql.append(" LEFT JOIN channel c ON pe.`channel`=c.`id`");
            sql.append(" WHERE pe.`is_delete`='NO'");

            sql.append(" AND pe.event_type ='VERIFIED'");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId AND c.`name` IS NOT NULL");
            if (type != null) {
                if (type == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(pe.date_created)=CURDATE()-1");
                }
                if (type == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(pe.date_created)");
                }
                if (type == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(pe.date_created)=QUARTER(now())");
                }
            } else {
                sql.append(" AND pe.date_created>=:startDate and pe.date_created<=:endDate");
                params.put("startDate", startDate);
                params.put("endDate", endDate);
            }
            sql.append(" GROUP BY pe.`channel`,pe.`date_created`");
            sql.append(") d ON d.id=ch.id  WHERE ch.`is_delete`='NO' AND ch.`mall_id`=:thirdMallId AND ch.`merchant_id`=:thirdMerchantId");

            params.put("thirdMallId", mallId);
            params.put("thirdMerchantId", merchantId);
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            fields.append(" c.id,c.`name` as channelName ,pe.`date_created` as dateCount ,COUNT(pe.`num`) as verificationCount");
            log.debug("channel static sql:" + sql.toString());
        };

        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 某一个产品在某一渠道的访问人数
     * @param productId
     * @param channelId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductViewCustomerCount(Long productId, Long channelId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("   AND pe.`event_type` IN('VIEWED','CLICKED')");
            sql.append("  AND  pe.`channel`=:channelId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);
            params.put("channelId", channelId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.market_id is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.market_id is not  null");
                }
            }


            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.type =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when SUM(pe.buyer_id) is null then 0 else  SUM(pe.buyer_id)  end");
            log.debug("channel static sql:" + sql.toString());
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     *  某一个产品在某一渠道的访问次数
     * @param productId
     * @param channelId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductViewCount(Long productId, Long channelId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("  AND pe.`channel`=:channelId");
            sql.append("   AND pe.`event_type` IN('VIEWED','CLICKED')");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);
            params.put("channelId", channelId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when count(pe.id) is null then 0 else  count(pe.id)  end");
            log.debug("channel static sql:" + sql.toString());
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 某一个产品在某一渠道的销售数量
     * @param productId
     * @param channelId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductSaleCount(Long productId, Long channelId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`channel`=:channelId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("   AND pe.`event_type`='SALED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("channelId", channelId);
            params.put("productId", productId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when sum(pe.num) is null then 0 else  sum(pe.num)  end");
            log.debug("channel static sql:" + sql.toString());
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 某一个产品在某一渠道的下单人数
     * @param productId
     * @param channelId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductOrderCustomerCount(Long productId, Long channelId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`channel`=:channelId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("   AND pe.`event_type`='ORDERED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);
            params.put("channelId", channelId);
            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when count(pe.buyer_id) is null then 0 else  count(pe.buyer_id)  end");
            log.debug("channel static sql:" + sql.toString());
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     *某一个产品在某一渠道的核销数
     * @param productId
     * @param channelId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductVerifyCount(Long productId, Long channelId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("  AND pe.`channel`=:channelId");
            sql.append("   AND pe.`event_type`='VERIFIED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);
            params.put("channelId", channelId);
            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when sum(pe.num) is null then 0 else  sum(pe.num)  end");
            log.debug("channel static sql:" + sql.toString());
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 某一个产品在某一渠道的核销人数
     * @param productId
     * @param channelId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductVerifyCustomerCount(Long productId, Long channelId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`channel`=:channelId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("   AND pe.`event_type`='VERIFIED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);
            params.put("channelId", channelId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when count(pe.buyer_id) is null then 0 else  count(pe.buyer_id)  end");
            log.debug("channel static sql:" + sql.toString());
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 产品总下单数
     * @param productId
     * @param sourceType
     * @param type
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getOrderTotalByProductIdAndMallIdAndMerchantId(Long productId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("   AND pe.`event_type`='ORDERED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when count(pe.id) is null then 0 else  count(pe.id)  end");
            log.debug("channel static sql:" + sql.toString());
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }


    /**
     * 产品总核销数
     * @param productId
     * @param sourceType
     * @param type
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getVerificationTotalByProductIdAndMallIdAndMerchantId(Long productId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("   AND pe.`event_type`='VERIFIED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" case when sum(pe.num) is null then 0 else  sum(pe.num)  end");
            log.debug("channel static sql:" + sql.toString());
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }


    @Override
    public Long getViewCountByChannelId(Long channelId, Long mallId, Long merchantId, String type, Long typeId, MerchantOrderSourceType sourceType, String startDate, String endDate) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {

            sql.append("select count(1) from (select %s from product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  LEFT JOIN channel c ON c.id=pe.`channel`");
            sql.append(" where pe.event_type='VIEWED'");
            sql.append(" and pe.is_delete='NO'");
            sql.append(" and pe.channel=:mallId");
            sql.append(" and pe.mall_id=:mallId");
            sql.append(" and pe.merchant_id=:merchantId");
            sql.append(" and pe.channel=:channelId");
            sql.append(" and pe.is_delete='NO')b");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("channelId", channelId);

            if (typeId != null) {
                sql.append("  AND c.`channel_type_id`=:typeId");
                params.put("typeId", typeId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != sourceType) {
                if (MerchantOrderSourceType.SELF == sourceType) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == sourceType) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }


            fields.append("distinct pe.buyer_id");
            log.debug("channel static sql:" + sql.toString());
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Double queryProductPriceByProductIdAndMallIdAndMerchantId(Long productId, Long channelId, String sourceType, String type, Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Double> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append("  LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append("  WHERE pe.is_delete='NO'");
            sql.append("  AND pe.`mall_id`=:mallId");
            sql.append("  AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`product_id`=:productId");
            sql.append("  AND pe.`channel`=:channelId");
            sql.append("  limit 1");
//            sql.append("   AND pe.`event_type`='VERIFIED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("productId", productId);
            params.put("channelId", channelId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:startDate");
                params.put("endDate", endDate);

            }
            fields.append(" p.sell_price AS price");
            log.debug("channel static sql:" + sql.toString());
        };

        return doing.go(Double.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 统计 通过渠道ID 渠道下单数
     * @param channelId
     * @param mallId
     * @param type
     * @param merchantId
     * @param typeId
     * @param orderSourceType
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getOrderCountByChannelId(Long channelId, Long mallId, String type, Long merchantId, Long typeId,StatististicsType statististicsType, String orderSourceType, String startDate, String endDate) {

        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" SELECT %s FROM merchant_order_item moi" +
                    "  LEFT JOIN merchant_order mo ON mo.`id`=moi.`merchant_order_id`" +
                    "  LEFT JOIN product p ON p.`id`=moi.`product_id`" +
                    "  WHERE moi.`is_delete`='NO'" +
                    " AND moi.`mall_id`=:mallId AND moi.`merchant_id`=:merchantId" +
                    " AND  moi.`channel`=:channelId" +
                    " AND p.`type`=:type" );

            params.put("mallId",mallId);
            params.put("merchantId",merchantId);
            params.put("channelId",channelId);
            if (statististicsType != null) {
                if (statististicsType == StatististicsType.YESTERDAY) {
                    sql.append(" AND DATE(mo.date_created)=CURDATE()-1");
                }
                if (statististicsType == StatististicsType.WEEK) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(mo.date_created)");
                }
                if (statististicsType == StatististicsType.MONTH) {
                    sql.append(" AND  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(mo.date_created)");
                }
                if (statististicsType == StatististicsType.QUATER) {
                    sql.append("  AND QUARTER(mo.date_created)=QUARTER(now())");
                }
            } else {
                if(startDate!=null) {
                    sql.append(" AND mo.date_created>=:startDate");
                    params.put("startDate", startDate);
                }
                if(endDate!=null) {
                    sql.append(" AND mo.date_created>=:endDate");
                    params.put("endDate", endDate);
                }
            }
            if (orderSourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(orderSourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(orderSourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(orderSourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }
            if (type != null && ProductSourceType.valueOf(type) != ProductSourceType.ALL) {
                sql.append("  AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND mo.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND mo.date_created<=:startDate");
                params.put("endDate", endDate);

            }
        fields.append("count(mo.id)");

        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 访问次数
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @return
     */
    @Override
    public Long channelViewCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM channel_event ce");
            sql.append(" LEFT JOIN channel c ON c.id=ce.channel_id");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" WHERE ce.is_delete='NO'");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND ce.`mall_id`=:mallId");
            sql.append(" AND ce.`merchant_id`=:merchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND ce.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND ce.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelId!=null && channelId>0) {
                sql.append(" AND ce.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            fields.append("count(1)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 访问次数 top 10
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param groupBy
     * @param channelIds
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> channelViewCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, String groupBy, List<Long> channelIds) {
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "date_format(ce.date_created,'%Y/%m/%d')";
            String groupName = "date(ce.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(ce.date_created,'%H')";
                groupName="date_format(ce.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(ce.date_created,'%Y/%m/%d')";
                groupName="date(ce.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(ce.date_created,'%Y/%m')";
                groupName="date_format(ce.date_created,'%Y/%m')";
            }
            String sqlStr="SELECT "+dateFormat+" as dateStr,count(1) as viewCount,c.name as channelName,c.id FROM channel_event ce";
            if("other".equals(groupBy)) {
                sqlStr = "SELECT count(1) as totalCount,c.id,c.name as channelName FROM channel_event ce";
            }
            sql.append(sqlStr);
            sql.append(" left join (");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0 and c.mall_id=:mallId and c.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.id in(select cp.channel_id from channel_product cp left join product p on p.id=cp.product_id where cp.is_delete='NO' and p.mall_id=:mallId and p.market_merchant_id=:merchantId)");
            sql.append(" and c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0");
            sql.append(" ) c on ce.channel_id=c.id");
            sql.append(" WHERE ce.is_delete='NO'");
            sql.append(" and c.id is not null");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND ce.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND ce.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND ce.channel_id in(:channelIds)");
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            if(groupBy.equals("other")) {
                sql.append(" group by c.id, c.name");
                sql.append(" order by count(1) desc limit 10");
            } else {
                sql.append(" group by c.id, c.name," + groupName);
                sql.append(" order by " + groupName + ",count(1) desc");
            }

        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 访问人数
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param channelId
     * @return
     */
    @Override
    public Long channelViewCustomerCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM channel_event ce");
            sql.append(" LEFT JOIN channel c ON c.id=ce.channel_id");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" WHERE ce.is_delete='NO'");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND ce.`mall_id`=:mallId");
            sql.append(" AND ce.`merchant_id`=:merchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND ce.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND ce.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelId!=null && channelId>0) {
                sql.append(" AND ce.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            fields.append("count(distinct ce.visit_customer_id)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 访问人数 top 10
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param groupBy
     * @param channelIds
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> channelViewCustomerCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, String groupBy, List<Long> channelIds) {
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(ce.date_created,'%Y/%m/%d')";
            String groupName = "date(ce.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(ce.date_created,'%H')";
                groupName="date_format(ce.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(ce.date_created,'%Y/%m/%d')";
                groupName="date(ce.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(ce.date_created,'%Y/%m')";
                groupName="date_format(ce.date_created,'%Y/%m')";
            }
            String sqlStr = "SELECT "+dateFormat+" as dateStr,count(distinct ce.visit_customer_id) as viewCustomer,c.name as channelName,c.id FROM channel_event ce";
            if("other".equals(groupBy)) {
                sqlStr="SELECT count(distinct ce.visit_customer_id) as totalCount,c.id,c.name as channelName FROM channel_event ce";
            }
            sql.append(sqlStr);
            sql.append(" left join (");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0 and c.mall_id=:mallId and c.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.id in(select cp.channel_id from channel_product cp left join product p on p.id=cp.product_id where cp.is_delete='NO' and p.mall_id=:mallId and p.market_merchant_id=:merchantId)");
            sql.append(" and c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0");
            sql.append(" ) c on ce.channel_id=c.id");
            sql.append(" WHERE ce.is_delete='NO'");
            sql.append(" and c.id is not null");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND ce.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND ce.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND ce.channel_id in(:channelIds)");
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            if(groupBy.equals("other")) {
                sql.append(" group by c.id, c.name");
                sql.append(" order by count(distinct ce.visit_customer_id) desc limit 10");
            } else {
                sql.append(" group by c.id, c.name," + groupName);
                sql.append(" order by " + groupName + ",count(distinct ce.visit_customer_id) desc");
            }
        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 销售数量
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param channelId
     * @return
     */
    @Override
    public Long channelSaleCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" left join order_event oe on moi.merchant_order_num=oe.merchant_order_num");
            sql.append(" LEFT JOIN channel c ON c.id=moi.channel_id");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND oe.event_type='PAYED'");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelId!=null && channelId>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            fields.append("sum(moi.buy_count)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 销售数量 top 10
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param groupBy
     * @param channelIds
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> channelSaleCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, String groupBy, List<Long> channelIds) {
     SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
            String groupName="date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }
            String sqlStr="SELECT "+dateFormat+" as dateStr, sum(moi.buy_count) as saleCount,c.name as channelName,c.id FROM merchant_order_item moi";
            if(groupBy.equals("other")) {
                sqlStr = "SELECT sum(moi.buy_count) as totalCount,c.id,c.name as channelName FROM merchant_order_item moi";
            }
            sql.append(sqlStr);
            sql.append(" left join (");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0 and c.mall_id=:mallId and c.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.id in(select cp.channel_id from channel_product cp left join product p on p.id=cp.product_id where cp.is_delete='NO' and p.mall_id=:mallId and p.market_merchant_id=:merchantId)");
            sql.append(" and c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0");
            sql.append(" ) c on moi.channel_id=c.id");
            sql.append(" left join order_event oe on moi.merchant_order_num=oe.merchant_order_num");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND oe.event_type='PAYED'");
            sql.append(" and moi.channel_id is not null");
            sql.append(" and c.id is not null");
            sql.append(" and moi.merchant_role in('SELF','SUPPLIER')");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND moi.channel_id in(:channelIds)");
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            if(groupBy.equals("other")) {
                sql.append(" group by c.id, c.name");
                sql.append(" order by sum(moi.buy_count) desc limit 10");
            } else {
                sql.append(" group by c.id, c.name," + groupName);
                sql.append(" order by " + groupName + ",sum(moi.buy_count) desc");
            }
        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 下单人数
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param channelId
     * @return
     */
    @Override
    public Long channelOrderCustomerCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" left join order_event oe on moi.merchant_order_num=oe.merchant_order_num");
            sql.append(" LEFT JOIN channel c ON c.id=moi.channel_id");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND oe.event_type='ORDERED'");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelId!=null && channelId>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            fields.append("count(distinct moi.customer_id)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 下单人数 top 10
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param groupBy
     * @param channelIds
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> channelOrderCustomerCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, String groupBy, List<Long> channelIds){
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(moi.date_created,'%Y/%m/%d')";
            String groupName = "date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }
            String sqlStr="SELECT "+dateFormat+" as dateStr, count(distinct moi.customer_id) as saleCustomerCount,c.name as channelName,c.id FROM merchant_order_item moi";
            if(groupBy.equals("other")) {
                sqlStr = "SELECT count(distinct moi.customer_id) as totalCount,c.id,c.name as channelName FROM merchant_order_item moi";
            }
            sql.append(sqlStr);
            sql.append(" left join (");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0 and c.mall_id=:mallId and c.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.id in(select cp.channel_id from channel_product cp left join product p on p.id=cp.product_id where cp.is_delete='NO' and p.mall_id=:mallId and p.market_merchant_id=:merchantId)");
            sql.append(" and c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0");
            sql.append(" ) c on moi.channel_id=c.id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.channel_id is not null");
            sql.append(" and c.id is not null");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND moi.channel_id in(:channelIds)");
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            if(groupBy.equals("other")) {
                sql.append(" group by c.id, c.name");
                sql.append(" order by count(distinct moi.customer_id) desc limit 10");
            } else {
                sql.append(" group by c.id, c.name," + groupName);
                sql.append(" order by " + groupName + ",count(distinct moi.customer_id) desc");
            }
        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销数量
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param channelId
     * @return
     */
    @Override
    public Long channelVerifyCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" LEFT JOIN channel c ON c.id=moi.channel_id");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);
            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelId!=null && channelId>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            fields.append("sum(moi.verify_count)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销数量 top 10
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param groupBy
     * @param channelIds
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> channelVerifyCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, String groupBy, List<Long> channelIds) {
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(moi.date_created,'%Y/%m/%d')";
            String groupName = "date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }
            String sqlStr="SELECT "+dateFormat+" as dateStr, sum(moi.verify_count) as verificationCount,c.name as channelName,c.id FROM merchant_order_item moi";
            if("other".equals(groupBy)) {
                sqlStr = "SELECT sum(moi.verify_count) as totalCount,c.id,c.name as channelName FROM merchant_order_item moi";
            }
            sql.append(sqlStr);
            sql.append(" left join (");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0 and c.mall_id=:mallId and c.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.id in(select cp.channel_id from channel_product cp left join product p on p.id=cp.product_id where cp.is_delete='NO' and p.mall_id=:mallId and p.market_merchant_id=:merchantId)");
            sql.append(" and c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0");
            sql.append(" ) c on moi.channel_id=c.id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" and moi.channel_id is not null");
            sql.append(" and c.id is not null");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);
            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND moi.channel_id in(:channelIds)");
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            if(groupBy.equals("other")) {
                sql.append(" group by c.id, c.name");
                sql.append(" order by sum(moi.verify_count) desc limit 10");
            } else {
                sql.append(" group by c.id, c.name," + groupName);
                sql.append(" order by " + groupName + ",sum(moi.verify_count) desc");
            }
        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销人数
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param channelId
     * @return
     */
    @Override
    public Long channelVerifyCustomerCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" LEFT JOIN channel c ON c.id=moi.channel_id");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelId!=null && channelId>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            fields.append("count(distinct mo.`customer_id`)");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销人数 top 10
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param groupBy
     * @param channelIds
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> channelVerifyCustomerCount(Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, String groupBy, List<Long> channelIds) {
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(moi.date_created,'%Y/%m/%d')";
            String groupName = "date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }
            String sqlStr = "SELECT "+dateFormat+" as dateStr, count(distinct moi.customer_id) as verificationCustomerCount,c.name as channelName,c.id FROM merchant_order_item moi";
            if("other".equals(groupBy)) {
                sqlStr = "SELECT count(distinct moi.customer_id) as totalCount,c.id,c.name as channelName FROM merchant_order_item moi";
            }
            sql.append(sqlStr);
            sql.append(" left join (");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0 and c.mall_id=:mallId and c.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select c.id,c.name,c.mall_id,c.merchant_id,c.channel_type_id from channel c left join channel_type ct on ct.id=c.channel_type_id");
            sql.append(" where c.id in(select cp.channel_id from channel_product cp left join product p on p.id=cp.product_id where cp.is_delete='NO' and p.mall_id=:mallId and p.market_merchant_id=:merchantId)");
            sql.append(" and c.is_delete='NO' and c.channel_status='USING' and c.is_system_default=0");
            sql.append(" ) c on moi.channel_id=c.id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" and moi.channel_id is not null");
            sql.append(" and c.id is not null");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND moi.channel_id in(:channelIds)");
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            if(groupBy.equals("other")) {
                sql.append(" group by c.id, c.name");
                sql.append(" order by count(distinct moi.customer_id) desc limit 10");
            } else {
                sql.append(" group by c.id, c.name," + groupName);
                sql.append(" order by "+groupName+",count(distinct moi.customer_id) desc");
            }
        };

        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 查找渠道列表
     * @param page
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @param typeIds
     * @param sourceType
     * @param channelIds
     * @return
     */
    @Override
    public PageResult<ChannelStatisticsChartDto> findChannels(PageResult page, Long mallId, Long merchantId, String startDate, String endDate, List<Long> typeIds, String sourceType, List<Long> channelIds) {
        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from (");
            sql.append("SELECT c.id, c.name as channelName,ct.type_name as typeName,c.mall_id as mallId,c.merchant_id as merchantId FROM channel c");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" WHERE c.is_delete='NO'");
            sql.append(" AND c.is_system_default = 0");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND c.`mall_id`=:mallId");
            sql.append(" AND c.`merchant_id`=:merchantId");
//            sql.append(" AND exists(select cp.channel_id from channel_product cp where c.id=cp.channel_id)");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

//            if (startDate != null) {
//                sql.append(" AND c.date_created>=:startDate");
//                params.put("startDate", startDate);
//
//            }
//            if (endDate != null) {
//                sql.append(" AND c.date_created<=:endDate");
//                params.put("endDate", endDate);
//
//            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            sql.append(" union ");
            sql.append("SELECT distinct c.id, c.name as channelName,ct.type_name as typeName,c.mall_id as mallId,c.merchant_id as merchantId FROM channel c");
            sql.append(" LEFT JOIN channel_type ct ON ct.id=c.channel_type_id");
            sql.append(" left join channel_product cp on c.id=cp.channel_id");
            sql.append(" left join (select a.id,a.mall_id,a.merchant_id from (");
            sql.append(" select p.id,p.mall_id,p.merchant_id from product p where p.market_id is null and p.mall_id=:mallId and p.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select pp.id,pp.mall_id,pp.merchant_id from product pp where pp.market_id is not null");
            sql.append(" and (pp.market_id in(select p.id from product p where p.market_id is null and p.mall_id=:mallId and p.merchant_id=:merchantId)");
            sql.append(" or (pp.mall_id=:mallId and pp.merchant_id=:merchantId))");
            sql.append(" ) a) pp on pp.id=cp.product_id");
            sql.append(" WHERE c.is_delete='NO'");
            sql.append(" AND c.is_system_default = 0");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND c.`mall_id`=pp.mall_id");
            sql.append(" AND c.`merchant_id`=pp.merchant_id");
//            sql.append(" AND exists(select cp.channel_id from channel_product cp where c.id=cp.channel_id)");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

//            if (startDate != null) {
//                sql.append(" AND c.date_created>=:startDate");
//                params.put("startDate", startDate);
//
//            }
//            if (endDate != null) {
//                sql.append(" AND c.date_created<=:endDate");
//                params.put("endDate", endDate);
//
//            }
            if(typeIds!=null && typeIds.size()>0) {
                sql.append(" AND c.channel_type_id in(:typeIds)");
                params.put("typeIds", typeIds);
            }
            if(channelIds!=null && channelIds.size()>0) {
                sql.append(" AND c.id in(:channelIds)");
                params.put("channelIds", channelIds);
            }
            sql.append(") a");
            fields.append("a.*");
//            fields.append("c.id, c.name as channelName,ct.type_name as typeName");
        };
        return doing.go(page, ChannelStatisticsChartDto.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * 查找渠道下产品品类
     * @param mallId
     * @param merchantId
     * @param channelId
     * @return
     */
    @Override
    public List<ChannelStatisticsChartDto> findChannelProductCategories(Long mallId, Long merchantId, Long channelId) {
        SQLListDoing<ChannelStatisticsChartDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM channel c");
            sql.append(" LEFT JOIN channel_product cp ON c.id=cp.channel_id");
            //sql.append(" LEFT JOIN product_categories pc ON cp.product_id=pc.product_id");
            sql.append(" WHERE c.is_delete='NO'");
            sql.append(" AND c.channel_status = 'USING'");
            sql.append(" AND c.`mall_id`=:mallId");
            sql.append(" AND c.`merchant_id`=:merchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND c.id=:channelId");
                params.put("channelId", channelId);
            }
            fields.append("distinct cp.product_id as id");
        };
        return doing.go(ChannelStatisticsChartDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 计算各渠道的访问量与销售量
     * @param page
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public PageResult<ChannelDto> calculateChannelsViewCountAndSaleCount(PageResult page, Long mallId, Long merchantId) {
        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from channel c");
            sql.append(" left join (select ce.channel_id,count(1) as viewCount from channel_event ce where 1=1");
            sql.append(" and date(ce.date_created)=curdate()-1");
            sql.append(" and exists(select 1 from channel c where c.mall_id=:mallId and c.merchant_id=:merchantId and c.is_system_default=0 and c.id=ce.channel_id)");
            sql.append(" group by ce.channel_id) a on c.id=a.channel_id");
            sql.append(" left join (select moi.channel_id as cid, sum(moi.buy_count) as saleCount from merchant_order_item moi");
            sql.append(" left join order_event oe on moi.merchant_order_num=oe.merchant_order_num");
            sql.append(" where moi.is_delete='NO' and moi.mall_id=:mallId and moi.merchant_id=:merchantId and oe.event_type='PAYED'");
            sql.append(" and date(moi.date_created)=curdate()-1");
            sql.append(" and exists(select 1 from channel c where c.mall_id=:mallId and c.merchant_id=:merchantId and c.is_system_default=0 and c.id=moi.channel_id)");
            sql.append(" group by moi.channel_id) b on c.id=b.cid");
            sql.append(" where c.mall_id=:mallId");
            sql.append(" and c.merchant_id=:merchantId");
            sql.append(" and c.is_delete='NO'");
            sql.append(" and c.is_system_default=0");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            sql.append(" order by a.viewCount, b.saleCount desc");
            fields.append("c.id,c.name,ifnull(a.viewCount,0) as viewCount,ifnull(b.saleCount,0) as saleCount");
        };
        return doing.go(page, ChannelDto.class, objectMapper, jdbcTemplate, log);
    }
}